#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "

drop database if exists db_dwd cascade;
create database if not exists db_dwd;

use db_dwd;
drop table if exists dim_class_studying_student_count;
CREATE TABLE if not exists dim_class_studying_student_count (
                id  INT ,
                school_id  INT  COMMENT '校区id',
                subject_id  INT  COMMENT '学科id',
                class_id  INT  COMMENT '班级id',
                studying_student_count  INT COMMENT '在读班级人数',
                studying_date  date  COMMENT '在读日期')
    COMMENT '在读学员人数信息表'
    partitioned by (dt string)--studying_date=dt
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='snappy');
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_class_studying_student_count partition(dt)
select * from db_ods.class_studying_student_count where class_id is not null  and studying_date is not null;

DROP TABLE if exists dim_course_table_upload_detail;
CREATE TABLE dim_course_table_upload_detail(
                id  INT  COMMENT 'id',
                base_id  INT    COMMENT '课程主表id',
                class_id  INT    COMMENT '班级id',
                class_date  date    COMMENT '上课日期',
                content  string        COMMENT '课程内容',
                teacher_id  INT    COMMENT '老师id',
                teacher_name  string        COMMENT '老师名字',
                job_number  string        COMMENT '工号',
                classroom_id  INT    COMMENT '教室id',
                classroom_name  string        COMMENT '教室名称',
                is_outline  INT    COMMENT '是否大纲 0 否 1 是',
                class_mode  INT    COMMENT '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播',
                is_stage_exam  INT    COMMENT '是否阶段考试（0：否 1：是）',
                is_pay  INT    COMMENT '代课费（0：无 1：有）',
                tutor_teacher_id  INT    COMMENT '晚自习辅导老师id',
                tutor_teacher_name  string        COMMENT '辅导老师姓名',
                tutor_job_number  string        COMMENT '晚自习辅导老师工号',
                is_subsidy  INT    COMMENT '晚自习补贴（0：无 1：有）',
                answer_teacher_id  INT    COMMENT '答疑老师id',
                answer_teacher_name  string        COMMENT '答疑老师姓名',
                answer_job_number string        COMMENT '答疑老师工号',
                remark string        COMMENT '备注',
                create_time  string  COMMENT '创建时间')
    COMMENT'班级排课信息表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='snappy');
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_course_table_upload_detail
select * from db_ods.course_table_upload_detail where class_date is not null  and class_id is not null;


DROP TABLE if exists facts_student_leave_apply;
CREATE TABLE facts_student_leave_apply (
         id  INT  ,
         class_id  INT     COMMENT '班级id',
         student_id  INT     COMMENT '学员id',
         audit_state  INT COMMENT '审核状态 0 待审核 1 通过 2 不通过',
         audit_person  INT     COMMENT '审核人',
         audit_time  string     COMMENT '审核时间',
         audit_remark  string     COMMENT '审核备注',
         leave_type  INT     COMMENT '请假类型  1 请假 2 销假',
         leave_reason  INT     COMMENT '请假原因  1 事假 2 病假',
         begin_time  string     COMMENT '请假开始时间',
         begin_time_type  INT     COMMENT '1：上午 2：下午',
         end_time  string     COMMENT '请假结束时间',
         end_time_type  INT     COMMENT '1：上午 2：下午',
         days  float     COMMENT '请假/已休天数',
         cancel_state  INT COMMENT '撤销状态  0 未撤销 1 已撤销',
         cancel_time  string     COMMENT '撤销时间',
         old_leave_id  INT     COMMENT '原请假id，只有leave_type =2 销假的时候才有',
         leave_remark  string    COMMENT '请假/销假说明',
         valid_state  INT COMMENT '是否有效（0：无效 1：有效）',
         create_time  string     COMMENT '创建时间')
    COMMENT'学生请假申请表'
    partitioned by (dt string) --dt = create_time
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='snappy');
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table facts_student_leave_apply partition (dt)
select * from db_ods.student_leave_apply where begin_time is not null  and class_id is not null and end_time is not null ;

DROP TABLE if exists dim_tbh_class_time_table;
CREATE TABLE dim_tbh_class_time_table (
          id   INT  COMMENT '主键id',
          class_id   INT   COMMENT '班级id',
          morning_template_id   INT   COMMENT '上午出勤模板id',
          morning_begin_time   string   COMMENT '上午开始时间',
          morning_end_time   string   COMMENT '上午结束时间',
          afternoon_template_id   INT   COMMENT '下午出勤模板id',
          afternoon_begin_time  string  COMMENT '下午开始时间',
          afternoon_end_time   string  COMMENT '下午结束时间',
          evening_template_id   INT   COMMENT '晚上出勤模板id',
          evening_begin_time   string   COMMENT '晚上开始时间',
          evening_end_time   string   COMMENT '晚上结束时间',
          use_begin_date   date   COMMENT '使用开始日期',
          use_end_date   date   COMMENT '使用结束日期',
          create_time   string  COMMENT '创建时间',
          create_person   INT   COMMENT '创建人',
          remark   string COMMENT '备注')
    COMMENT'班级作息时间表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='snappy');
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_tbh_class_time_table
select * from db_ods.tbh_class_time_table where morning_begin_time is not null  and class_id is not null ;

DROP TABLE if exists facts_tbh_student_signin_record;
CREATE TABLE facts_tbh_student_signin_record (
                   id   INT  COMMENT '主键id',
                   normal_class_flag   INT   COMMENT '是否正课 1 正课 2 自习',
                   time_table_id   INT   COMMENT '作息时间id 关联tbh_school_time_table 或者 tbh_class_time_table',
                   class_id   INT   COMMENT '班级id',
                   student_id   INT   COMMENT '学员id',
                   signin_time string   COMMENT '签到时间',
                   signin_date date   COMMENT '签到日期',
                   inner_flag   INT   COMMENT '内外网标志  0 外网 1 内网',
                   signin_type   INT   COMMENT '签到类型 1 心跳打卡 2 老师补卡',
                   share_state   INT COMMENT '共享屏幕状态 0 否 1是  在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0 ',
                   inner_ip string  COMMENT '内网ip地址')
    COMMENT '学生打卡记录表'
    partitioned by (dt string) --dt =  signin_date
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress'='snappy');

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table facts_tbh_student_signin_record partition (dt)
select * from db_ods.tbh_student_signin_record where signin_date is not null  and class_id is not null and id is not null ;

"